﻿using Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class Dal
    {
        //查询全部书籍
        static public List<Book> select_over()
        {
            string sql = "select [ID],[BookName],[Author],[Press],[Price],[Pic],[Type],[Introduced],Show from BookInfo ";
            DataTable dt = DBHelper.GetDataTable(sql);
            List<Book> books = new List<Book>();
            foreach (DataRow dr in dt.Rows)
            {
                Book book = new Book();
                book.ID = Convert.ToInt32(dr["ID"]);
                book.bookname = dr["bookname"].ToString();
                book.Author = dr["Author"].ToString();
                book.Press = dr["Press"].ToString();
                book.Price = Convert.ToDecimal(dr["Price"]);
                book.Pic = dr["Pic"].ToString();
                book.Type = Convert.ToInt32(dr["Type"]);
                book.Introduced = dr["Introduced"].ToString();
                book.Show = (int)dr["Show"];
                books.Add(book);
            }
            return books;
        }

        //根据类型ID查询前四书籍
        static public List<Book> books_typeid_top4(int id)
        {
            string sql = "select top 4 [ID],[BookName],[Author],[Press],[Price],[Pic],[Type],[Introduced] from BookInfo where [Type] in ( select ID from BookTypeSon where Show=1 and BooktypeID =" + id + ")";
            DataTable dt = DBHelper.GetDataTable(sql);
            List<Book> books = new List<Book>();
            foreach (DataRow dr in dt.Rows)
            {
                Book book = new Book();
                book.ID = Convert.ToInt32(dr["ID"]);
                book.bookname = dr["bookname"].ToString();
                book.Author = dr["Author"].ToString();
                book.Press = dr["Press"].ToString();
                book.Price = Convert.ToDecimal(dr["Price"]);
                book.Pic = dr["Pic"].ToString();
                book.Type = Convert.ToInt32(dr["Type"]);
                book.Introduced = dr["Introduced"].ToString();
                books.Add(book);
            }
            return books;
        }

        //根据类型id查询
        static public List<Book> books_typeid(int id)
        {
            string sql = "select [ID],[BookName],[Author],[Press],[Price],[Pic],[Type],[Introduced] from BookInfo where Show=1 and [Type] in ( select ID from BookTypeSon where BooktypeID =" + id + ")";
            DataTable dt = DBHelper.GetDataTable(sql);
            List<Book> books = new List<Book>();
            foreach (DataRow dr in dt.Rows)
            {
                Book book = new Book();
                book.ID = Convert.ToInt32(dr["ID"]);
                book.bookname = dr["bookname"].ToString();
                book.Author = dr["Author"].ToString();
                book.Press = dr["Press"].ToString();
                book.Price = Convert.ToDecimal(dr["Price"]);
                book.Pic = dr["Pic"].ToString();
                book.Type = Convert.ToInt32(dr["Type"]);
                book.Introduced = dr["Introduced"].ToString();
                books.Add(book);
            }
            return books;
        }

        //查询特定单本书籍
        static public Book book_id(int id)
        {
            string sql = "select [ID],[BookName],[Author],[Press],[Price],[Pic],[Type],[Introduced] from BookInfo where Show=1 and ID=" + id;
            DataTable dt = DBHelper.GetDataTable(sql);
            Book book = new Book();
            foreach (DataRow dr in dt.Rows)
            {
                book.ID = Convert.ToInt32(dt.Rows[0]["ID"]);
                book.bookname = dt.Rows[0]["bookname"].ToString();
                book.Author = dt.Rows[0]["Author"].ToString();
                book.Press = dt.Rows[0]["Press"].ToString();
                book.Price = Convert.ToDecimal(dt.Rows[0]["Price"]);
                book.Pic = dt.Rows[0]["Pic"].ToString();
                book.Type = Convert.ToInt32(dt.Rows[0]["Type"]);
                book.Introduced = dt.Rows[0]["Introduced"].ToString();
            }
            return book;
        }

        //搜索
        static public List<Book> select_key(string key)
        {
            string sql = "select [ID],[BookName],[Author],[Press],[Price],[Pic],[Type],[Introduced] from BookInfo where Show=1 and BookName like '%" + key + "%'";
            DataTable dt = DBHelper.GetDataTable(sql);
            List<Book> books = new List<Book>();
            foreach (DataRow dr in dt.Rows)
            {
                Book book = new Book();
                book.ID = Convert.ToInt32(dr["ID"]);
                book.bookname = dr["bookname"].ToString();
                book.Author = dr["Author"].ToString();
                book.Press = dr["Press"].ToString();
                book.Price = Convert.ToDecimal(dr["Price"]);
                book.Pic = dr["Pic"].ToString();
                book.Type = Convert.ToInt32(dr["Type"]);
                book.Introduced = dr["Introduced"].ToString();
                books.Add(book);
            }
            return books;
        }

        //查询评论
        static public List<Ping> select_ping(int bookid)
        {
            string sql = "select UserName,UserInfo.ID,Comments,Avatar,[Time] from Comments,UserInfo where BookID= " + bookid + " and UserInfo.ID = UserID";
            DataTable dt = DBHelper.GetDataTable(sql);
            List<Ping> ping = new List<Ping>();
            foreach (DataRow dr in dt.Rows)
            {
                Ping p = new Ping();
                p.username = dr["UserName"].ToString();
                p.userid = Convert.ToInt32(dr["ID"]);
                p.ping = dr["Comments"].ToString();
                p.pic = dr["Avatar"].ToString();
                p.dt = dr["Time"].ToString();
                ping.Add(p);
            }
            return ping;
        }

        //评论
        static public bool insert_ping(int bookid, int userid, string ping, string dt)
        {
            string sql = "insert [Comments] values (" + bookid + "," + userid + ",'" + ping + "','" + dt + "')";
            bool i = DBHelper.Execute(sql);
            return i;
        }

        //添加订单
        static public bool insert_order(int id, string orderid, string dt)
        {
            string sql = "insert  [Order] values (" + id + ",'" + orderid + "', '" + dt + "')";
            bool i = DBHelper.Execute(sql);
            return i;
        }

        //添加详情订单
        static public bool insert_order_det(string orderid, decimal price, int bookid)
        {
            string sql = "INSERT INTO [Orderdetails] VALUES ('" + orderid + "'," + price + ", " + bookid + ")";
            bool i = DBHelper.Execute(sql);
            return i;
        }

        //收藏
        static public bool insert_shou(int bookid, int userid)
        {
            string sql = "insert [Collection] values (" + bookid + "," + userid + ")";
            bool i = DBHelper.Execute(sql);
            return i;
        }

        //通过用户ID查询用户收藏数书籍
        static public List<Book> select_shou(int userid)
        {
            string sql = "select [ID],[BookName],[Author],[Press],[Price],[Pic],[Type],[Introduced] from BookInfo where ID in (select [BookID] from [Collection] where [UserID] =" + userid + ")";
            DataTable dt = DBHelper.GetDataTable(sql);
            List<Book> books = new List<Book>();
            foreach (DataRow dr in dt.Rows)
            {
                Book book = new Book();
                book.ID = Convert.ToInt32(dr["ID"]);
                book.bookname = dr["bookname"].ToString();
                book.Author = dr["Author"].ToString();
                book.Press = dr["Press"].ToString();
                book.Price = Convert.ToDecimal(dr["Price"]);
                book.Pic = dr["Pic"].ToString();
                book.Type = Convert.ToInt32(dr["Type"]);
                book.Introduced = dr["Introduced"].ToString();
                books.Add(book);
            }
            return books;
        }

        //删除收藏
        static public bool delete_shou(int bookid, int userid)
        {
            string sql = "delete from [Collection] where [BookID] =" + bookid + " and [UserID] ="+userid;
            bool i = DBHelper.Execute(sql);
            return i;
        }

        //查询订单
        static public List<Order> order_book(int userid)
        {
            //string sql = "select [Order].[OederID] ,[Order].[Time],[Orderdetails].[Price],BookInfo.BookName,BookInfo.Press,BookInfo.Pic from [Order] ,[Orderdetails],BookInfo where [Order].[UserID]="+userid+" and [Order].[OederID] = [Orderdetails].[OederID] and Orderdetails.BookID = BookInfo.ID";
            string sql = "select [Order].[OederID] ,[Order].[Time] from [Order] where [Order].[UserID]="+userid;
            DataTable dt = DBHelper.GetDataTable(sql);
            List<Order> orders = new List<Order>();
            foreach(DataRow dr in dt.Rows )
            {
                Order order = new Order();
                order.OederID = dr["OederID"].ToString();
                order.Time = dr["Time"].ToString();

                string ssql = "select [Orderdetails].[Price],BookInfo.BookName,BookInfo.Press,BookInfo.Pic from [Orderdetails],BookInfo where Orderdetails.BookID = BookInfo.ID and Orderdetails.[OederID]='" + dr["OederID"].ToString() + "'";
                DataTable dtt = DBHelper.GetDataTable(ssql);
                List<Orderde> orderde =new List<Orderde> ();
                foreach (DataRow drr in dtt.Rows) 
                {
                    Orderde oo = new Orderde();
                    oo.pric = Convert.ToDecimal(drr["Price"]);
                    oo.bookname = drr["BookName"].ToString();
                    oo.press = drr["Press"].ToString();
                    oo.pic = drr["Pic"].ToString();
                    orderde.Add(oo);
                }
                order.orderde = orderde;
                orders.Add(order);
            }
            return orders;
        }

        //查询我的书籍
        static public List<Book> select_mybook(int userid)
        {
            string sql = "select [ID],[BookName],[Author],[Press],[Price],[Pic],[Type],[Introduced] from BookInfo where ID in (select [Orderdetails].[BookID] from [Order] ,[Orderdetails] where [Order].[OederID] = [Orderdetails].[OederID] and [Order].[UserID]="+userid+")";
            DataTable dt = DBHelper.GetDataTable(sql);
            List<Book> books = new List<Book>();
            foreach (DataRow dr in dt.Rows)
            {
                Book book = new Book();
                book.ID = Convert.ToInt32(dr["ID"]);
                book.bookname = dr["bookname"].ToString();
                book.Author = dr["Author"].ToString();
                book.Press = dr["Press"].ToString();
                book.Price = Convert.ToDecimal(dr["Price"]);
                book.Pic = dr["Pic"].ToString();
                book.Type = Convert.ToInt32(dr["Type"]);
                book.Introduced = dr["Introduced"].ToString();
                books.Add(book);
            }
            return books;
        }

        //修改图书信息
        static public bool update(int id, string Author, string bookname, string Press, string price, string Introduced)
        {
            string sql = "UPDATE BookInfo SET Author='" + Author + "',BookName='" + bookname + "',Press='" + Press + "',Price=" + price + ",Introduced='" + Introduced + "' WHERE ID = " + id;
            bool i = DBHelper.Execute(sql);
            return i;
        }

        //上下架图书
        static public bool delete(int id, int show)
        {
            string sql = "UPDATE BookInfo SET Show= "+show+" where ID ="+ id;
            bool i = DBHelper.Execute(sql);
            return i;
        }

        //查询子类型
        static public List<type> select_type()
        {
            string sql = "select ID,TypeName from BookTypeSon";
            DataTable dt = DBHelper.GetDataTable(sql);
            List<type> types = new List<type>();
            foreach (DataRow dr in dt.Rows)
            {
                type ty = new type();
                ty.typeid = (int)dr["ID"];
                ty.typename = dr["TypeName"].ToString();
                types.Add(ty);
            }
            return types;
        }
    }
}
